/*
Copyright 2024 MySQL Parser Project

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package main

import (
	"fmt"
	"log"

	"mysql-parser/sqlparser"
)

func main() {
	fmt.Println("=== 复杂查询示例 ===")
	fmt.Println()

	// 综合复杂查询示例
	fmt.Println("=== 综合复杂查询示例 ===")
	complexQueries := []string{
		// 多表JOIN + 聚合 + 子查询
		`SELECT 
			u.name,
			COUNT(DISTINCT o.id) as order_count,
			SUM(o.total) as total_spent,
			AVG(p.price) as avg_product_price
		FROM users u
		LEFT JOIN orders o ON u.id = o.user_id
		LEFT JOIN order_items oi ON o.id = oi.order_id
		LEFT JOIN products p ON oi.product_id = p.id
		WHERE u.created_at > '2023-01-01'
			AND u.id IN (
				SELECT user_id 
				FROM subscriptions 
				WHERE status = 'active'
			)
		GROUP BY u.id, u.name
		HAVING COUNT(DISTINCT o.id) > 2
		ORDER BY total_spent DESC
		LIMIT 10`,

		// 窗口函数 + 子查询
		`SELECT 
			name,
			price,
			category_id,
			ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as price_rank,
			price - (SELECT AVG(price) FROM products p2 WHERE p2.category_id = p1.category_id) as price_diff
		FROM products p1
		WHERE price > (
			SELECT AVG(price) * 1.2 
			FROM products p3 
			WHERE p3.category_id = p1.category_id
		)`,

		// CTE (Common Table Expression) 模拟
		`SELECT 
			user_stats.user_id,
			user_stats.total_orders,
			user_stats.total_spent,
			product_stats.favorite_category
		FROM (
			SELECT 
				u.id as user_id,
				COUNT(o.id) as total_orders,
				SUM(o.total) as total_spent
			FROM users u
			LEFT JOIN orders o ON u.id = o.user_id
			GROUP BY u.id
		) as user_stats
		JOIN (
			SELECT 
				o.user_id,
				p.category_id as favorite_category,
				ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY COUNT(*) DESC) as rn
			FROM orders o
			JOIN order_items oi ON o.id = oi.order_id
			JOIN products p ON oi.product_id = p.id
			GROUP BY o.user_id, p.category_id
		) as product_stats ON user_stats.user_id = product_stats.user_id
		WHERE product_stats.rn = 1
			AND user_stats.total_orders > 5`,

		// 递归查询模拟（分层数据）
		`SELECT 
			e1.id,
			e1.name,
			e1.manager_id,
			e2.name as manager_name,
			(
				SELECT COUNT(*) 
				FROM employees e3 
				WHERE e3.manager_id = e1.id
			) as direct_reports,
			CASE 
				WHEN e1.manager_id IS NULL THEN 'CEO'
				WHEN EXISTS (
					SELECT 1 
					FROM employees e4 
					WHERE e4.manager_id = e1.id
				) THEN 'Manager'
				ELSE 'Employee'
			END as role_type
		FROM employees e1
		LEFT JOIN employees e2 ON e1.manager_id = e2.id
		ORDER BY e1.manager_id, e1.name`,

		// 数据透视查询模拟
		`SELECT 
			DATE_FORMAT(o.created_at, '%Y-%m') as month,
			SUM(CASE WHEN p.category_id = 1 THEN oi.quantity * oi.price ELSE 0 END) as electronics_sales,
			SUM(CASE WHEN p.category_id = 2 THEN oi.quantity * oi.price ELSE 0 END) as books_sales,
			SUM(CASE WHEN p.category_id = 3 THEN oi.quantity * oi.price ELSE 0 END) as clothing_sales,
			SUM(oi.quantity * oi.price) as total_sales,
			COUNT(DISTINCT o.id) as total_orders,
			COUNT(DISTINCT o.user_id) as unique_customers
		FROM orders o
		JOIN order_items oi ON o.id = oi.order_id
		JOIN products p ON oi.product_id = p.id
		WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
		GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
		ORDER BY month DESC`,
	}

	for i, sql := range complexQueries {
		fmt.Printf("复杂查询示例 %d:\n%s\n", i+1, sql)
		
		// 解析SQL
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		// 提取表名
		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("涉及的表: %v\n", tables)

		// 表分析
		tableAnalysis := sqlparser.AnalyzeTables(stmt)
		fmt.Printf("表分析: 表数量=%d, 有连接=%v, 有子查询=%v\n",
			tableAnalysis.TableCount, tableAnalysis.HasJoins, tableAnalysis.HasSubquery)

		// 聚合分析
		aggAnalysis := sqlparser.AnalyzeAggregation(stmt)
		fmt.Printf("聚合分析: 包含聚合=%v, 有GROUP BY=%v, 有HAVING=%v, 聚合函数=%v\n",
			aggAnalysis.HasAggregation, aggAnalysis.HasGroupBy, aggAnalysis.HasHaving, aggAnalysis.Functions)

		// 查询复杂度分析
		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println("---")
		fmt.Println()
	}

	// 性能分析查询示例
	fmt.Println("=== 性能分析查询示例 ===")
	performanceQueries := []string{
		// 慢查询分析
		`SELECT 
			u.name,
			COUNT(*) as query_count,
			AVG(execution_time) as avg_execution_time,
			MAX(execution_time) as max_execution_time
		FROM query_logs ql
		JOIN users u ON ql.user_id = u.id
		WHERE ql.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
			AND ql.execution_time > 1000
		GROUP BY u.id, u.name
		HAVING COUNT(*) > 10
		ORDER BY avg_execution_time DESC`,

		// 索引使用分析
		`SELECT 
			table_name,
			index_name,
			COUNT(*) as usage_count,
			AVG(rows_examined) as avg_rows_examined,
			AVG(rows_sent) as avg_rows_sent
		FROM index_usage_stats
		WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
		GROUP BY table_name, index_name
		HAVING COUNT(*) > 100
		ORDER BY usage_count DESC`,

		// 表大小分析
		`SELECT 
			table_name,
			COUNT(*) as row_count,
			AVG(CHAR_LENGTH(CONCAT_WS('', column1, column2, column3))) as avg_row_size,
			COUNT(*) * AVG(CHAR_LENGTH(CONCAT_WS('', column1, column2, column3))) as estimated_size
		FROM information_schema.tables t
		JOIN table_data td ON t.table_name = td.table_name
		WHERE t.table_schema = 'mydb'
		GROUP BY table_name
		ORDER BY estimated_size DESC`,
	}

	for i, sql := range performanceQueries {
		fmt.Printf("性能分析查询示例 %d:\n%s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("涉及的表: %v\n", tables)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println("---")
		fmt.Println()
	}

	// 数据仓库风格查询示例
	fmt.Println("=== 数据仓库风格查询示例 ===")
	dwQueries := []string{
		// 时间序列分析
		`SELECT 
			DATE(created_at) as date,
			COUNT(*) as daily_orders,
			SUM(total) as daily_revenue,
			AVG(total) as avg_order_value,
			COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE(created_at)) as order_growth,
			SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE(created_at)) as revenue_growth
		FROM orders
		WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
		GROUP BY DATE(created_at)
		ORDER BY date`,

		// 用户行为分析
		`SELECT 
			u.id,
			u.name,
			COUNT(DISTINCT DATE(o.created_at)) as active_days,
			COUNT(o.id) as total_orders,
			SUM(o.total) as lifetime_value,
			DATEDIFF(MAX(o.created_at), MIN(o.created_at)) as customer_lifespan,
			CASE 
				WHEN SUM(o.total) > 10000 THEN 'VIP'
				WHEN SUM(o.total) > 5000 THEN 'Premium'
				WHEN SUM(o.total) > 1000 THEN 'Regular'
				ELSE 'Basic'
			END as customer_tier
		FROM users u
		LEFT JOIN orders o ON u.id = o.user_id
		WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
		GROUP BY u.id, u.name
		HAVING COUNT(o.id) > 0
		ORDER BY lifetime_value DESC`,

		// 产品推荐分析
		`SELECT 
			p1.id as product_id,
			p1.name as product_name,
			p2.id as recommended_product_id,
			p2.name as recommended_product_name,
			COUNT(*) as co_purchase_count,
			COUNT(*) / (
				SELECT COUNT(DISTINCT oi1.order_id) 
				FROM order_items oi1 
				WHERE oi1.product_id = p1.id
			) as recommendation_strength
		FROM products p1
		JOIN order_items oi1 ON p1.id = oi1.product_id
		JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id != oi2.product_id
		JOIN products p2 ON oi2.product_id = p2.id
		GROUP BY p1.id, p1.name, p2.id, p2.name
		HAVING COUNT(*) >= 5
		ORDER BY p1.id, recommendation_strength DESC`,
	}

	for i, sql := range dwQueries {
		fmt.Printf("数据仓库查询示例 %d:\n%s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v (可能包含不支持的窗口函数)\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("涉及的表: %v\n", tables)

		complexity := sqlparser.AnalyzeQueryComplexity(stmt)
		fmt.Printf("复杂度: %s (分数: %d)\n",
			complexity.ComplexityLevel, complexity.ComplexityScore)

		fmt.Println("---")
		fmt.Println()
	}
}